﻿
using Dapper;
using System;
using BCEntity.Sim.Admin.StaffOrganizationMappingRecord;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;

namespace BCData.Sim.Admin.StaffOrganizationMappingRecord
{
    /// <summary>
    /// 员工组织映射记录
    /// </summary>
    public class StaffOrganizationMappingRecordData : IStaffOrganizationMappingRecordData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM StaffOrganizationMappingRecord";
        private const string QUERY = @"SELECT StaffOrganizationMappingRecordId,PreLoginTime,AdminId,FullName,CompanyId,CompanyName,DepartmentId,DepartmentName,JobDuty,JobTitle,JobDutyKey,JobTitleKey,IsActive,LastLoginTime 
                                        FROM StaffOrganizationMappingRecord";
        private const string QUERY_VIEW = @"SELECT A.StaffOrganizationMappingRecordId,A.PreLoginTime,A.AdminId,A.FullName,A.CompanyId,A.CompanyName,A.DepartmentId,A.DepartmentName,A.JobDuty,A.JobTitle,A.JobDutyKey,
		                                            A.JobTitleKey,A.IsActive,A.LastLoginTime,B.Avatar
                                            FROM StaffOrganizationMappingRecord A
                                            INNER JOIN Admin B ON A.AdminId=B.AdminId";
        private const string QUERY_NONE_STAFF_VIEW = @"SELECT A.LastLoginTime AS PreLoginTime,A.AdminId,A.FullName,A.CompanyId,A.CompanyName,A.DepartmentId,A.DepartmentName,A.JobDuty,A.JobTitle,A.JobDutyKey,
	                                                        A.JobTitleKey,A.Avatar
                                                        FROM Admin A 
                                                        LEFT JOIN StaffOrganizationMappingRecord B ON A.AdminId=B.AdminId AND B.DepartmentId=?DepartmentId
                                                        WHERE B.AdminId IS NULL";
        public StaffOrganizationMappingRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<bool> Exists(long staffOrganizationMappingRecordId)
        {
            string sql = string.Format(@"{0} WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?StaffOrganizationMappingRecordId", staffOrganizationMappingRecordId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        public async Task<bool> Exists(int companyId, int departmentId, long adminId)
        {
            string sql = string.Format(@"{0} WHERE CompanyId=?CompanyId AND DepartmentId=?DepartmentId AND AdminId=?AdminId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32, size: 11);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32, size: 11);
            parameters.Add("?AdminId", adminId, DbType.Int64, size: 20);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        public async Task<bool> InUse(string jobTitleNo)
        {
            string sql = string.Format(@"{0} WHERE JobTitleKey=?JobTitleNo LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?JobTitleNo", jobTitleNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> Exists(string departmentPath, long adminId)
        {
            string sql = @"SELECT 1 FROM StaffOrganizationMappingRecord A
                            INNER JOIN Department B ON A.DepartmentId=B.DepartmentId
                            WHERE (FIND_IN_SET(A.DepartmentId,?DepartmentPath) OR LEFT(B.DepartmentPath,LENGTH(?DepartmentPath))=?DepartmentPath)
                                AND A.AdminId=?AdminId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentPath", departmentPath, DbType.AnsiString);
            parameters.Add("?AdminId", adminId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> InUse(int companyId, string positionNo)
        {
            string sql = string.Format(@"{0} WHERE CompanyId=?CompanyId AND JobDutyKey=?PositionNo LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            parameters.Add("?PositionNo", positionNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<StaffOrganizationMappingRecordEntity> Add(StaffOrganizationMappingRecordEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("StaffOrganizationMappingRecord", new string[] { "AdminId", "FullName", "CompanyId", "CompanyName", "DepartmentId", "DepartmentName",
                "JobDuty","JobTitle","JobDutyKey","JobTitleKey","IsActive" });
            sql = string.Format("{0} {1} WHERE StaffOrganizationMappingRecordId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?FullName", entity.FullName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int32, size: 11);
            parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?JobDuty", entity.JobDuty, DbType.AnsiString, size: 50);
            parameters.Add("?JobTitle", entity.JobTitle, DbType.AnsiString, size: 50);
            parameters.Add("?JobDutyKey", entity.JobDutyKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobTitleKey", entity.JobTitleKey, DbType.AnsiString, size: 150);
            parameters.Add("?IsActive", entity.IsActive, DbType.Boolean);

            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<StaffOrganizationMappingRecordEntity>(sql, parameters, databaseContext.Transaction);

        }

        public async Task<StaffOrganizationMappingRecordEntity> Update(StaffOrganizationMappingRecordEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("StaffOrganizationMappingRecord", new string[] { "PreLoginTime", "AdminId", "FullName", "CompanyId", "CompanyName", "DepartmentId",
                "DepartmentName", "JobDuty","JobTitle","JobDutyKey","JobTitleKey","IsActive", "LastLoginTime" }, new string[] { "StaffOrganizationMappingRecordId" });
            sql = string.Format("{0} {1} WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?StaffOrganizationMappingRecordId", entity.StaffOrganizationMappingRecordId, DbType.Int64, size: 20);
            parameters.Add("?PreLoginTime", entity.PreLoginTime, DbType.DateTime);
            parameters.Add("?AdminId", entity.AdminId, DbType.Int64, size: 20);
            parameters.Add("?FullName", entity.FullName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 11);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentId", entity.DepartmentId, DbType.Int32, size: 11);
            parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?JobDuty", entity.JobDuty, DbType.AnsiString, size: 50);
            parameters.Add("?JobTitle", entity.JobTitle, DbType.AnsiString, size: 50);
            parameters.Add("?JobDutyKey", entity.JobDutyKey, DbType.AnsiString, size: 150);
            parameters.Add("?JobTitleKey", entity.JobTitleKey, DbType.AnsiString, size: 150);
            parameters.Add("?IsActive", entity.IsActive, DbType.Boolean);
            parameters.Add("?LastLoginTime", entity.LastLoginTime, DbType.DateTime);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<StaffOrganizationMappingRecordEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> Delete(long staffOrganizationMappingRecordId)
        {
            string sql = @"DELETE FROM StaffOrganizationMappingRecord WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?StaffOrganizationMappingRecordId", staffOrganizationMappingRecordId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public void SetActiveRole(long staffOrganizationMappingRecordId)
        {
            string sql = @"SELECT AdminId FROM StaffOrganizationMappingRecord WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId LIMIT 1 INTO @AdminId;
                           UPDATE StaffOrganizationMappingRecord SET IsActive=0 WHERE AdminId=@AdminId;
                           UPDATE StaffOrganizationMappingRecord SET PreLoginTime=LastLoginTime, IsActive=1, LastLoginTime=NOW() 
                           WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?StaffOrganizationMappingRecordId", staffOrganizationMappingRecordId, DbType.Int64, size: 20);
            this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
        }

        public async Task<StaffOrganizationMappingRecordEntity> GetEntity(long staffOrganizationMappingRecordId)
        {
            string sql = string.Format(@"{0} WHERE StaffOrganizationMappingRecordId=?StaffOrganizationMappingRecordId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?StaffOrganizationMappingRecordId", staffOrganizationMappingRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<StaffOrganizationMappingRecordEntity>(sql, parameters);
        }

        public async Task<StaffOrganizationMappingRecordEntity> GetActiveEntity(long adminId)
        {
            string sql = string.Format(@"{0} WHERE AdminId=?AdminId AND IsActive=1 LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<StaffOrganizationMappingRecordEntity>(sql, parameters);
        }

        public async Task<StaffOrganizationMappingRecordEntity> GetEntity(string departmentPath, long adminId)
        {
            string sql = @"SELECT A.StaffOrganizationMappingRecordId,A.PreLoginTime,A.AdminId,A.FullName,A.CompanyId,A.CompanyName,A.DepartmentId,
                                A.DepartmentName,A.JobDuty,A.JobTitle,A.JobDutyKey,A.JobTitleKey,A.IsActive,A.LastLoginTime 
                            FROM StaffOrganizationMappingRecord A 
                            INNER JOIN Department B ON A.DepartmentId=B.DepartmentId
                            WHERE (FIND_IN_SET(A.DepartmentId,?DepartmentPath) OR LEFT(B.DepartmentPath,LENGTH(?DepartmentPath))=?DepartmentPath)
                                AND A.AdminId=?AdminId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentPath", departmentPath, DbType.AnsiString);
            parameters.Add("?AdminId", adminId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<StaffOrganizationMappingRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetList(long adminId)
        {
            string sql = string.Format("{0} WHERE A.AdminId=?AdminId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminId", adminId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }
        public async Task<IEnumerable<StaffViewEntity>> GetList(long[] adminIds)
        {
            string sql = string.Format("{0} WHERE FIND_IN_SET(A.AdminId,?AdminIds)", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AdminIds", string.Join(',', adminIds), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetCompanyStaffList(int companyId)
        {
            string sql = string.Format("{0} WHERE A.CompanyId=?CompanyId GROUP BY A.AdminId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetDepartmentStaffList(int departmentId)
        {
            string sql = string.Format("{0} INNER JOIN Department C ON A.DepartmentId=C.DepartmentId", QUERY_VIEW);
            sql = string.Format("{0} WHERE FIND_IN_SET(?DepartmentId,C.DepartmentPath) GROUP BY A.AdminId", sql);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetCurrentlyDepartmentStaffList(int departmentId)
        {
            string sql = string.Format("{0} WHERE A.DepartmentId=?DepartmentId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetNoneStaffList(int companyId, int departmentId)
        {
            string sql = string.Format("{0} AND A.CompanyId=?CompanyId GROUP BY A.AdminId", QUERY_NONE_STAFF_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", companyId, DbType.Int32);
            parameters.Add("?DepartmentId", departmentId, DbType.Int32);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<StaffViewEntity>> GetJobTitleStaffList(string jobTitleKey)
        {
            string sql = string.Format("{0} WHERE A.JobTitleKey=?JobTitleKey GROUP BY A.AdminId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?JobTitleKey", jobTitleKey, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }
        public async Task<IEnumerable<StaffViewEntity>> GetPositionStaffList(string position)
        {
            string sql = string.Format("{0} WHERE A.JobDutyKey=?JobDutyKey GROUP BY A.AdminId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?JobDutyKey", position, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        
        public async Task<IEnumerable<StaffViewEntity>> GetStaffList(string staffName)
        {
            string sql = string.Format("{0} WHERE B.AccountStatus='Using' AND B.FullName LIKE CONCAT('%',CONCAT(?FullName,'%')) GROUP BY A.AdminId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FullName", staffName, DbType.AnsiString, size: 20);
            return await this.databaseContext.Slave.QueryAsync<StaffViewEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<StaffOrganizationMappingRecordEntity>, int)> Query(string companyName, string departmentName, string adminName, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(companyName))
            {
                wheres.Add("CompanyName LIKE CONCAT('%',CONCAT(?CompanyName,'%'))");
                parameters.Add("?CompanyName", companyName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(departmentName))
            {
                wheres.Add("DepartmentName LIKE CONCAT('%',CONCAT(?DepartmentName,'%'))");
                parameters.Add("?DepartmentName", departmentName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(adminName))
            {
                wheres.Add("FullName LIKE CONCAT('%',CONCAT(?FullName,'%'))");
                parameters.Add("?FullName", adminName, DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("LastLoginTime>=?Start");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("LastLoginTime<=?End");
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("LastLoginTime>=?Start AND LastLoginTime<=?End");
                parameters.Add("?Start", start.Value.Date, DbType.DateTime);
                parameters.Add("?End", end.Value.AddDays(1).Date, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY LastLoginTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM StaffOrganizationMappingRecord {1}",
                   QUERY,
                   wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                   (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                   pageSize);
            return await this.databaseContext.Slave.QueryMultipleAsync<StaffOrganizationMappingRecordEntity, int>(sql, parameters);
        }
    }
}



